﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Wei8808Cms.Model;
using Wei8808Cms.Utility;

namespace Wei8808Cms.DAL
{
    public class CaseDAL : DbHelperBase
    {
        public int GetCount()
        {
            var strSql = new StringBuilder();
            strSql.Append(" SELECT COUNT(*) AS [Total] FROM [Case] WHERE 1=1 ");
            var count = 0;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["Total"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public DataSet GetPageList(Pagination qcmodel)
        {
            var ds = new DataSet();
            var strSql = new StringBuilder();
            var start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            var end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by CreateTime) AS idx,[CaseId],[CaseName],[CaseRemark],[CaseImg],[IsRec] FROM [Case] ");
            strSql.Append(" WHERE 1=1 ");
            strSql.Append(" ) AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    ds = Db.ExecuteDataSet(cmd);
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return ds;
        }

        public bool Add(CaseInfo model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" INSERT INTO [Case]([CaseName],[CaseRemark],[CaseDummyImg],[CaseImg],[IsRec],[CreateUser],[CreateTime]) ");
            strSql.Append(" VALUES(@CaseName,@CaseRemark,@CaseDummyImg,@CaseImg,@IsRec,@CreateUser,@CreateTime) ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@CaseName", model.CaseName);
                    Db.AddInParameter(cmd, "@CaseRemark", model.CaseRemark);
                    Db.AddInParameter(cmd, "@CaseDummyImg", model.CaseDummyImg);
                    Db.AddInParameter(cmd, "@CaseImg", model.CaseImg);
                    Db.AddInParameter(cmd, "@IsRec", model.IsRec);
                    Db.AddInParameter(cmd, "@CreateUser", model.CreateUser);
                    Db.AddInParameter(cmd, "@CreateTime", model.CreateTime);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool Delete(List<CaseInfo> list)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" DELETE FROM [Case] WHERE CaseId=@CaseId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@CaseId", DbType.Int32);
                    foreach (var i in list)
                    {
                        cmd.Parameters["@CaseId"].Value = i.CaseId;
                        Db.ExecuteNonQuery(cmd);
                    }
                    result = true;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public CaseInfo GetCaseInfo(int id)
        {
            var model = new CaseInfo();
            object obj = DBNull.Value;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT [CaseId],[CaseName],[CaseRemark],[CaseDummyImg],[CaseImg],[IsRec] FROM [Case] WHERE CaseId=@CaseId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@CaseId", id);
                    using (var reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {

                            model.CaseName = reader["CaseName"] == obj ? null : Convert.ToString(reader["CaseName"]);
                            model.CaseRemark = reader["CaseRemark"] == obj ? null : Convert.ToString(reader["CaseRemark"]);
                            model.CaseDummyImg = reader["CaseDummyImg"] == obj ? null : Convert.ToString(reader["CaseDummyImg"]);
                            model.CaseImg = reader["CaseImg"] == obj ? null : Convert.ToString(reader["CaseImg"]);
                            if (reader["IsRec"] != DBNull.Value)
                                model.IsRec = Convert.ToInt32(reader["IsRec"]);
                            if (reader["CaseId"] != DBNull.Value)
                                model.CaseId = Convert.ToInt32(reader["CaseId"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return model;
            }
        }

        public bool Update(CaseInfo model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE [Case] SET [CaseName] = @CaseName,[CaseRemark] = @CaseRemark,[CaseDummyImg] = @CaseDummyImg,[CaseImg] = @CaseImg,[IsRec] = @IsRec,[ModifyUser] = @ModifyUser,[ModifyTime] = @ModifyTime  WHERE CaseId=@CaseId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@CaseName", model.CaseName);
                    Db.AddInParameter(cmd, "@CaseRemark", model.CaseRemark);
                    Db.AddInParameter(cmd, "@CaseDummyImg", model.CaseDummyImg);
                    Db.AddInParameter(cmd, "@CaseImg", model.CaseImg);
                    Db.AddInParameter(cmd, "@IsRec", model.IsRec);
                    Db.AddInParameter(cmd, "@ModifyUser", model.ModifyUser);
                    Db.AddInParameter(cmd, "@ModifyTime", model.ModifyTime);
                    Db.AddInParameter(cmd, "@CaseId", model.CaseId);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool UpdateField(int id, string strField)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE News set " + strField);
            strSql.Append(" where CaseId=@CaseId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@CaseId", id);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public List<CaseInfo> GetCaseList(int pageIndex, int pageSize)
        {
            var strSql = new StringBuilder();
            var list = new List<CaseInfo>();
            object obj = DBNull.Value;
            int start = (pageIndex - 1) * (pageSize + 1);
            int end = pageSize + start;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by CreateTime desc) AS idx,[CaseId],[CaseName],[CaseRemark],[CaseDummyImg],[CaseImg] FROM [Case] ");
            strSql.Append(" where IsRec=1 ) AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new CaseInfo();
                            if (reader["CaseId"] != DBNull.Value)
                                model.CaseId = Convert.ToInt32(reader["CaseId"]);
                            model.CaseName = reader["CaseName"] == obj ? null : Convert.ToString(reader["CaseName"]);
                            model.CaseRemark = reader["CaseRemark"] == obj ? null : Convert.ToString(reader["CaseRemark"]);
                            model.CaseDummyImg = reader["CaseDummyImg"] == obj ? null : Convert.ToString(reader["CaseDummyImg"]);
                            model.CaseImg = reader["CaseImg"] == obj ? null : Convert.ToString(reader["CaseImg"]);
                            list.Add(model);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public int GetTotal(string id)
        {
            var strSql = new StringBuilder();
            strSql.Append("SELECT COUNT(*) AS [Total] FROM [Case] ");
            var count = 0;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["Total"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }
    }
}
